<?php

class Home_model extends CI_Model {

	function __construct() {
		parent::__construct();
	}
	
	function get_offers($fb_id)
	{
		$sql = "SELECT * FROM offer AS o, category AS c, sub_category AS s WHERE o.fb_id = ? AND o.display = 1 AND o.category_id = c.category_id AND o.sub_category_id = s.sub_category_id";
		$query = $this->db->query($sql, $fb_id);
		
		$data = array();
		if ($query->num_rows() > 0)
		{
			foreach($query->result_array() as $row)
			{
				$data[] = $row;
			}
		}
		
		return $data;
	}
	
	function get_requests($fb_id)  
	{
		$sql = "SELECT c.*, s.*, o.item_desc, r.offer_id, item_pic, item_title, r.status, o.fb_id FROM request AS r JOIN offer AS o ON r.offer_id = o.offer_id JOIN category AS c ON o.category_id = c.category_id JOIN sub_category AS s ON o.sub_category_id = s.sub_category_id  WHERE r.fb_id = ? AND r.display=1";
		
		$query = $this->db->query($sql, $fb_id);
		
		$data = array();
		if ($query->num_rows() > 0)
		{
			foreach($query->result_array() as $row)
			{
				$temp1 = $row;
				$temp1['owner'] = $row["fb_id"];
				$data[] = $temp1;
			}
		}
		return $data;
	}
	
	function get_offer_details($offer_id)
	{
		$sql = "SELECT * FROM offer WHERE offer_id = ?";
		
		$query = $this->db->query($sql, $offer_id);
		
		$data = array();
		if ($query->num_rows() > 0)
		{
			foreach($query->result_array() as $row)
			{
				$data[] = $row;
			}
		}
		return $data;
	}
	
	function get_new_items($fb_id)
	{
		$i=0;
		$id_array = array();
		$query = $this->db->query("SELECT category_id FROM category_preference WHERE fb_id = ".$fb_id); // get category preferences
		$pre_array = array();
		if ($query->num_rows() > 0)
		{
			foreach($query->result_array() as $row)
				$pre_array[] = $row['category_id'];
		}
		$preference = implode("','",$pre_array);
		
		$query = $this->db->query("SELECT area_id FROM area_preference WHERE fb_id = ".$fb_id); // get area preferences too
		$area_array = array();
		if ($query->num_rows() > 0)
		{
			foreach($query->result_array() as $row)
				$area_array[] = $row['area_id'];
		}
		$area = implode("','",$area_array);
		
		
		$oneweek = date('Y-m-d H:i:s',strtotime('- 3 week')); //define recent as one week for now
		
		//first grab items that satisfy both category and area preferences
		$query = "SELECT * FROM offer AS o 
					JOIN area_preference AS a ON o.fb_id = a.fb_id 
					WHERE o.status = 'Available' 
					AND o.date_created >= ?
					AND o.category_id IN (?) 
					AND a.area_id IN (?)
					AND o.fb_id <> ? ORDER BY date_created DESC";
					
		$result = $this->db->query($query, array ($oneweek, $preference, $area, $fb_id));
		$data = array();
		if ($result->num_rows() > 0)
		{
			foreach($result->result_array() as $row)
			{
				$data[] = $row;
				$id_array[] = $row['offer_id'];
				$i++;
			}
		}
		
		if ($i<18)
		{
			// next grab all offers that satisfy only category preferences
			$query = "SELECT * FROM offer AS o WHERE o.date_created >= ? AND o.fb_id <> ? AND o.status = 'Available' AND o.category_id IN (?) ORDER BY date_created DESC";
			$result = $this->db->query($query, array($oneweek, $fb_id, $preference));
			if ($result->num_rows() > 0)
			{
				foreach($result->result_array() as $row)
				{
					if (!in_array($row['offer_id'], $id_array))
					{
						$data[] = $row;
						$id_array[] = $row['offer_id'];
						$i++;
						if ($i>=18)
							break;
					}
				}
			}
		}
		
		if ($i<18)
		{
			// next grab all offers that satisfy only area preferences
			$query = "SELECT * FROM offer AS o
						JOIN area_preference AS a ON o.fb_id = a.fb_id
						WHERE o.status = 'Available' 
						AND o.date_created >= ? 
						AND a.area_id IN (?)
						AND o.fb_id <> ? ORDER BY date_created DESC";
						
			$result = $this->db->query($query, array($oneweek, $area, $fb_id));
			if ($result->num_rows() > 0)
			{
				foreach($result->result_array() as $row)
				{
					if (!in_array($row['offer_id'], $id_array))
					{
						$data[] = $row;
						$id_array[] = $row['offer_id'];
						$i++;
						if ($i>=18)
							break;
					}
				}
			}
		}
		
		if ($i<18)
		{
			// finally grab the rest that doesn't fit into criteria
			$query = "SELECT * FROM offer AS o WHERE o.date_created >= '".$oneweek."' AND o.fb_id <> ".$fb_id." AND o.status = 'Available' ORDER BY date_created DESC";
			
			$result = $this->db->query($query);
			if ($result->num_rows() > 0)
			{
				foreach($result->result_array() as $row)
				{
					if (!in_array($row['offer_id'], $id_array))
					{
						$data[] = $row;
						$id_array[] = $row['offer_id'];
						$i++;
						if ($i>=18)
							break;
					}
				}
			}
		}
		
		return $data;
	}
	
	function get_status($type, $fb_id, $id)
	{
		$sql = "SELECT * FROM ? WHERE fb_id= ? AND offer_id= ?";
		
		$data = array();
		$query = $this->db->query($sql, array($type, $fb_id, $id));
		if ($query->num_rows() > 0)
		{
			foreach($query->result_array() as $row)
			{
				$data[] = $row;
			}
		}
		return $data;
	}
	
	function get_owner($id)
	{
		$sql = "SELECT fb_id FROM offer WHERE offer_id= ?";
		$query = $this->db->query($sql, $id);
		$data = array();
		foreach ($query->result_array() as $row)
		{
			$data[] = $row;
		}
		return $data;
	}
	
	function remove_from_database($type, $fb_id, $id)
	{
		$this->db->delete($type, array('fb_id' => $fb_id, 'offer_id' => $id));
	}
	
	function get_all_other($table, $type, $id)
	{
		$sql = "SELECT fb_id FROM ".$table." WHERE offer_id=? AND status=?";
		$data = array();
		$query = $this->db->query($sql, array($id, $type));
		if ($query->num_rows() > 0)
		{
			foreach($query->result_array() as $row)
			{
				$data[] = $row;
			}
		}
		return $data;
	}
	
	function change_status($table, $id, $from, $to, $others=null)
	{
		if ($others)
			$array = array_merge(array('status' => $to), $others);
		else
			$array = array('status' => $to);
		
		$this->db->where('offer_id', $id);
		$this->db->where('status', $from);
		$this->db->update($table, $array);
	}
	
	function hide_from_view($type, $fb_id, $id)
	{
		$this->db->where('fb_id', $fb_id);
		$this->db->where('offer_id', $id);
		$this->db->update($type, array('display' => 0)); 
	}
	
}

?>